/**
 * @author guysagy
 */
package webSiteDataStore;

import java.sql.PreparedStatement;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import model.Profile;
import model.User;

public class UserData
{
    private static final Logger theLogger = Logger.getLogger(UserData.class.getName());

    public static int insertUser(User user) throws IOException, SQLException
    {
       int userId = -1;
       if (user == null
                || user.getUserName().length() == 0
                || user.getPassword() == 0)
       {
           return userId;
       }

       Connection connection = null;
       PreparedStatement preparedStatement = null;
       boolean success = false;

       try
       {
       	   connection = ConnectionPool.getConnection();
           // Could have used the auto-increment property of the userId column
           // but I want to experiment with this way of doing it -
           String sqlStatement = "SELECT MAX(userId) FROM Users;";
           preparedStatement = connection.prepareStatement(sqlStatement);
           preparedStatement.executeQuery();
           ResultSet resultSet1 = preparedStatement.getResultSet();
           userId = (resultSet1.last() == true) ? (resultSet1.getInt("MAX(userId)") + 1) : 1;
           
           connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
           connection.setAutoCommit(false);
           sqlStatement = "INSERT INTO Users (userId, userName, password) VALUES(?,?,?);";
           preparedStatement = connection.prepareStatement(sqlStatement);
           preparedStatement.setInt(1, userId);
           preparedStatement.setString(2, user.getUserName());
           preparedStatement.setInt(3, user.getPassword());
           int rowsAffected = preparedStatement.executeUpdate();
           success = (rowsAffected == 1);
           if (success)
           {
                user.setUserId(userId);
                user.getProfile().setUserId(userId);
                insertUserProfile(user);
                connection.commit();
           }
           else // if(!success)
           {
               StringBuilder msg = new StringBuilder("Failed to insert new user, userName: ").append(user.getUserName()).append(", password: ").append(user.getPassword());
               throw new SQLException(msg.toString());
           }

           return userId;
        }
        catch(SQLException e)
        {
           return -2;
        }
        finally
        {
            if (!success && connection != null)
            {
                connection.rollback();
            }


            SafeFreeResource.closeStatement(preparedStatement);
            
            if (connection != null)
            {
                connection.setAutoCommit(true);
                SafeFreeResource.closeConnection(connection);
            }
        }
    }

    public static boolean updateUserProfile(int userId, Profile profile) throws SQLException
    {
       if (profile == null
                || userId == -1
                || profile.getFirstName().length() == 0
                || profile.getLastName().length() == 0
                || profile.getEmailAddress().length() == 0)
       {
           return false;
       }

       boolean success = false;
       Connection connection = null;
       PreparedStatement preparedStatement = null;

       try
       {
           connection = ConnectionPool.getConnection();
           String sqlStatement = "UPDATE UsersProfiles SET firstName = ? , lastName = ? , emailAddress = ? WHERE userId = ?;";
           preparedStatement = connection.prepareStatement(sqlStatement);
           preparedStatement.setString(1, profile.getFirstName());
           preparedStatement.setString(2, profile.getLastName());
           preparedStatement.setString(3, profile.getEmailAddress());
           preparedStatement.setInt(4, userId);
           int rowsAffected = preparedStatement.executeUpdate();
           success = (rowsAffected==1);
           if (rowsAffected == 0)
           {
               StringBuilder msg = new StringBuilder("Failed to update user profile where userId: ").append(userId);
               Logger.getLogger(UserData.class.getName()).log(Level.SEVERE, msg.toString());
           }
           else if(rowsAffected > 1)
           {
               StringBuilder msg = new StringBuilder("Multiple users profile where updated for update user where userId: ").append(userId);
               Logger.getLogger(UserData.class.getName()).log(Level.SEVERE, msg.toString());
           }
        }
        catch(SQLException e)
        {
            theLogger.log(Level.SEVERE, null, e);
        }
        finally
        {
            SafeFreeResource.closeStatement(preparedStatement);
            SafeFreeResource.closeConnection(connection);
        }
        return success;
    }

    public static int authenticateUser(User user) throws IOException, SQLException
    {
        int userId = -1;
        if (user == null
                || user.getUserName().length() == 0
                || user.getPassword() == 0)
        {
           return userId;
        }

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try
        {
            connection = ConnectionPool.getConnection();
            String sqlStatement = "SELECT userId FROM Users WHERE userName = ? and password = ?;";
            preparedStatement = connection.prepareStatement(sqlStatement);
            preparedStatement.setString(1, user.getUserName());
            preparedStatement.setInt(2, user.getPassword());
            preparedStatement.executeQuery();
            resultSet = preparedStatement.getResultSet();
            int countOfRows = 0;
            if (resultSet.last())
                countOfRows = resultSet.getRow();

            if (countOfRows == 1)
            {
                userId = resultSet.getInt("userId");
            }
            else if (countOfRows > 1)
            {
                StringBuilder msg = new StringBuilder("Multiple users with same userName ").append(user.getUserName()).append(" and password ").append(user.getPassword());
                theLogger.log(Level.SEVERE, msg.toString());
            }
        }
        catch(SQLException e)
        {
            theLogger.log(Level.SEVERE, null, e);
        }
        finally
        {
            SafeFreeResource.closeResultSet(resultSet);
            SafeFreeResource.closeStatement(preparedStatement);
            SafeFreeResource.closeConnection(connection);
        }
        return userId;
    }

    public static boolean loadProfile(User user)
    {
        if (user == null || user.getUserId() == -1)
        {
           return false;
        }

        Connection connection = null;
        PreparedStatement preparedStatement =  null;
        ResultSet resultSet = null;
        boolean success  = false;

        try
        {
            connection = ConnectionPool.getConnection();
            String sqlStatement = "SELECT * FROM UsersProfiles WHERE userId = ?;";
            preparedStatement = connection.prepareStatement(sqlStatement);
            preparedStatement.setInt(1, user.getUserId());
            preparedStatement.executeQuery();
            resultSet = preparedStatement.getResultSet();
            int countOfRows = 0;
            if (resultSet.last())
                countOfRows = resultSet.getRow();

            success  = (countOfRows == 1);
            if (countOfRows == 1)
            {
                user.getProfile().setFirstName(resultSet.getString("firstName"));
                user.getProfile().setLastName(resultSet.getString("lastName"));
                user.getProfile().setEmailAddress(resultSet.getString("emailAddress"));
            }
            else if (countOfRows == 0)
            {
                StringBuilder msg = new StringBuilder("Could not load profile for user where userId: ").append(user.getUserId());
                theLogger.log(Level.SEVERE, msg.toString());
            }
            else if (countOfRows > 1)
            {
                StringBuilder msg = new StringBuilder("Found multiple profiles for user where userId: ").append(user.getUserId());
                theLogger.log(Level.SEVERE, msg.toString());
            }
        }
        catch(SQLException e)
        {
            theLogger.log(Level.SEVERE, null, e);
        }
        finally
        {
            SafeFreeResource.closeResultSet(resultSet);
            SafeFreeResource.closeStatement(preparedStatement);
            SafeFreeResource.closeConnection(connection);
        }
        return success;
    }


    // private methods /////////////////

    private static int getUserId(User user) throws IOException, SQLException
    {
        return authenticateUser(user);
    }

    private static boolean insertUserProfile(User user) 
    {
        Profile profile = user.getProfile();
        if (profile == null
            || profile.getUserId() == -1
            || profile.getFirstName().length() == 0
            || profile.getLastName().length() == 0
            || profile.getEmailAddress().length() == 0)
        {
            return false;
        }

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        boolean success = false;

        try
        {
            connection = ConnectionPool.getConnection();
            String sqlStatement = "INSERT INTO UsersProfiles (userId, firstName,lastName, emailAddress) VALUES(?,?,?,?);";
            preparedStatement = connection.prepareStatement(sqlStatement);
            preparedStatement.setInt(1, profile.getUserId());
            preparedStatement.setString(2, profile.getFirstName());
            preparedStatement.setString(3, profile.getLastName());
            preparedStatement.setString(4, profile.getEmailAddress());
            int rowsAffected = preparedStatement.executeUpdate();
            success = (rowsAffected == 1);
            if (!success)
            {
                StringBuilder msg = new StringBuilder("Zero or multiple rows affected when inserting profile for user where userId: ").append(user.getUserId());
                theLogger.log(Level.SEVERE, msg.toString());
            }
        }
        catch(SQLException e)
        {
            theLogger.log(Level.SEVERE, null, e);
        }
        finally
        {
            SafeFreeResource.closeStatement(preparedStatement);
            SafeFreeResource.closeConnection(connection);
        }
        return success;
    }
}
